import math
import os
import time
import sys

from tqdm import tqdm

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class SyncNonStandardDefault:
    def __init__(self, logger: Logger, localDb: ProdDb, dataCenterDb: CcxDataCenter, fcdb: FCDB):
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb
        self.dataCenterDb = dataCenterDb
        self.size = 1000

    def __loadNonStandardDefaultRegions__(self):
        sql = """
                SELECT
                    * 
                FROM
                    (
                    SELECT
                        credit_code,
                        `level`,
                        province,
                        province_code,
                        city,
                        city_code,
                        county,
                        county_code,
                        is_platform,
                        ROW_NUMBER() OVER ( PARTITION BY credit_code ORDER BY create_time DESC ) AS `no` 
                    FROM
                        base_non_standard_default 
                    WHERE
                        `status` = 1 
                    ) t 
                WHERE
                    t.`no` = 1        
        """
        return self.dataCenterDb.Query(sql)

    def __loadRegionData__(self, creditCodes):
        sql = """
            SELECT cc.OUTCODE ,ci.REGION
            FROM 
            tq_comp_codecor cc 
            left join tq_comp_info ci ON cc.COMPCODE = ci.COMPCODE
            WHERE
            cc.CTYPE = 1 
            AND cc.OUTCODE IN %s
        """
        return self.fcdb.Query(sql, (creditCodes,))

    def __loadRegions__(self):
        sql = """
            SELECT `code`, `name` FROM regions
        """
        return self.dataCenterDb.Query(sql)

    def __getOriginDataTotal__(self, tmstamp):
        sql = """
                SELECT count(1) as total FROM tq_bd_accbilloddetails where tmstamp > %s
            """
        r = self.fcdb.QueryOne(sql, (tmstamp,))
        if r is None:
            return 0
        else:
            return r["total"]

    def __getMaxTmstamp__(self):
        sql = """
            select max(tmstamp) as tmstamp from fc_accbilloddetails
        """
        r = self.localDb.QueryOne(sql)
        if r is None or r["tmstamp"] is None:
            return 0
        else:
            return r["tmstamp"]

    def __loadOriginData__(self, tmstamp, page, size):
        """
        小批量加载数据，分散数据库压力
        """
        sql = """
            SELECT
                ID,
                DECLAREDATE,
                COMPNAME,
                CREDITCODE,
                ENDDATE,
                TACCEPTHAPPAMT,
                ACCEPTAMT,
                TOVERDUEHAPPAMT,
                OVERAMOUNT,
                TMSTAMP 
            FROM
                tq_bd_accbilloddetails 
            WHERE
                TMSTAMP > %s
            ORDER BY
                TMSTAMP 
            LIMIT %s, %s
        """
        return self.fcdb.Query(sql, (tmstamp, size * page, size))

    def __update__(self, updateDatas):
        sql = """
            REPLACE INTO fc_accbilloddetails
            (id, declaredate,compname, creditcode, enddate, taccepthappamt, 
            acceptamt, toverduehappamt, overamount, tmstamp, `level`, region, 
            region_code, province, province_code, city, city_code, 
            county, county_code, is_platform)
            VALUES
            (%(id)s,%(declaredate)s,%(compname)s,%(creditcode)s,%(enddate)s,%(taccepthappamt)s
            ,%(acceptamt)s,%(toverduehappamt)s,%(overamount)s,%(tmstamp)s,%(level)s,%(region)s,
            %(region_code)s,%(province)s,%(province_code)s,%(city)s,%(city_code)s,
            %(county)s,%(county_code)s,%(is_platform)s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __genUpdateData__(self, fcData, regionDataMap, regionsMap, nonStandardDefaultMap):

        # 财汇数据
        updateData = {
            "id": fcData["ID"],
            "declaredate": fcData["DECLAREDATE"],
            "compname": fcData["COMPNAME"],
            "creditcode": fcData["CREDITCODE"],
            "enddate": fcData["ENDDATE"],
            "taccepthappamt": fcData["TACCEPTHAPPAMT"],
            "acceptamt": fcData["ACCEPTAMT"],
            "toverduehappamt": fcData["TOVERDUEHAPPAMT"],
            "overamount": fcData["OVERAMOUNT"],
            "tmstamp": fcData["TMSTAMP"]
        }

        # 产业企业信息
        regionCode = self.__getRegion__(regionDataMap, fcData["CREDITCODE"])
        updateData["region"] = regionsMap[regionCode] if regionCode in regionsMap.keys() else None
        updateData["region_code"] = regionCode

        # 城投企业信息
        dcData = nonStandardDefaultMap[fcData["CREDITCODE"]] if fcData["CREDITCODE"] in nonStandardDefaultMap.keys() else None
        if dcData is not None:
            updateData["level"] = dcData["level"]
            updateData["province"] = dcData["province"]
            updateData["province_code"] = dcData["province_code"]
            updateData["city"] = dcData["city"]
            updateData["city_code"] = dcData["city_code"]
            updateData["county"] = dcData["county"]
            updateData["county_code"] = dcData["county_code"]
            updateData["is_platform"] = dcData["is_platform"]
        else:
            updateData["level"] = None
            updateData["province"] = None
            updateData["province_code"] = None
            updateData["city"] = None
            updateData["city_code"] = None
            updateData["county"] = None
            updateData["county_code"] = None
            updateData["is_platform"] = None

        return updateData

    @staticmethod
    def __getRegion__(regionDataMap: dict, creditCode: str):
        if creditCode in regionDataMap.keys():
            regionCode = regionDataMap.get(creditCode)
            if regionCode.startswith("CN"):
                return regionCode[2:]
        return None

    def run(self):

        # 获取本地tmstamp
        tmstamp = self.__getMaxTmstamp__()

        # 统计待同步数据
        total = self.__getOriginDataTotal__(tmstamp=tmstamp)
        self.logger.info(f"待同步数据总数：{total}")

        if total == 0:
            self.logger.info("没有需要同步的数据")
            return

        # 加载数据中心区域
        self.logger.info("加载数据中心区域")
        regions = self.__loadRegions__()
        regionsMap = {r["code"]: r["name"] for r in regions}
        self.logger.info(f"加载数据中心区域 {len(regions)} 条")

        # 加载非标数据区域信息
        self.logger.info("加载非标数据区域信息")
        regions = self.__loadNonStandardDefaultRegions__()
        nonStandardDefaultMap = {r["credit_code"]: r for r in regions}
        self.logger.info(f"加载非标数据区域信息 {len(nonStandardDefaultMap)} 条")

        regionDataMap = {}

        pages = math.ceil(total / self.size)
        cnt = 0
        for page in tqdm(range(pages), desc="pages", file=sys.stdout):

            # 加载财汇票据逾期数据
            originDataList = self.__loadOriginData__(tmstamp=tmstamp, page=page, size=self.size)
            updateDatas = []

            # 加载财汇企业区域
            creditCodes = list(set(d["CREDITCODE"] for d in originDataList if d["CREDITCODE"] not in regionDataMap.keys()))
            if len(creditCodes) > 0:
                regionDataList = self.__loadRegionData__(creditCodes)
                current = {r["OUTCODE"]: r["REGION"] for r in regionDataList}
                regionDataMap.update(current)

            # 数据封装
            for d in originDataList:
                updateData = self.__genUpdateData__(fcData=d,
                                                    regionDataMap=regionDataMap,
                                                    regionsMap=regionsMap,
                                                    nonStandardDefaultMap=nonStandardDefaultMap)
                updateDatas.append(updateData)

            # 数据入库
            self.__update__(updateDatas)
            cnt += len(originDataList)
        self.logger.info(f"实际同步数据 {cnt} 条")


# 主函数
def main():
    logger = Logger()
    localDb = ProdDb(logSql=False, autocommit=True)
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    cleanScreen()
    t = time.perf_counter()

    logger.info("========商票逾期导入 开始 ========")

    # 同步主体名单
    sync = SyncNonStandardDefault(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb, fcdb=fcdb)
    sync.run()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("========商票逾期导入 结束 ========")


# 程序入口
if __name__ == "__main__":
    main()
